const db = require('../tool/db.js');


//添加商品分类的sql语句
async function add(data) {
    console.log(data);
        // 验证分类名的格式
        if (!data.name || data.name.length < 2 || data.name.length > 10) {
            return { code: 0, msg: '分类名的长度应在2-10个字符之间', data: '' }
        }
        if (data.parent_id.length == 0) {
            return { code: 0, msg: '请添加分类', data: '' }
        }
        
        let sql = `INSERT INTO category (name,parent_id,imgurl) VALUES (?,?,?)`
        let [result] = await db.query(sql, [data.name,data.parent_id,data.imgurl])
        if (result.insertId) {
            return { code: 1, msg: '添加成功', data: result.insertId }
        } else {
            return { code: 0, msg: '添加 失败', data: '' }
        }
    }
/**
 * 获取分类列表
 * @param {*} page 当前页码
 * @param {*} num 每页数量
 * @param {*} options 筛选条件
 */
 async function list(page, num, options) {
    let where1=`WHERE parent_id = '${options.parent_id}'`
    let offset = (page - 1) * num;
    let sql = `SELECT * FROM category ${where1} ORDER BY id DESC  LIMIT ${offset},${num}`
    let [lists] = await db.query(sql);
    let [[{ total }]] = await db.query(`SELECT COUNT(*) as total FROM category ${where1}`)
    return { lists, total }
}

//获取父级分类
async function list1( options) {
    let where1=`WHERE id = '${options.cid}'`
    let sql = `SELECT parent_id FROM category ${where1}`
    let [[lists1]] = await db.query(sql)
    let [[res]] = await db.query(`select * from category where id = ${lists1.parent_id}`)
    console.log(res);
    return { res }

}
/**
 * 
 * @param {*} page 当前页码
 * @param {*} num 每页数量
 * @param {*} options 筛选条件
 * @returns 
 */
async function query(page, num, options) {
    let where = 'WHERE 1'
    if (options.name) {
        where += ` AND name like "%${options.name}%"`
    }
    let offset = (page - 1) * num;
    let sql = `SELECT * FROM category ${where}  LIMIT ${offset},${num}`
    
    let [lists] = await db.query(sql);

    let [[{ total }]] = await db.query(`SELECT COUNT(*) as total FROM category ${where}`)
    return { lists, total }

}

 
async function mod(data) {
    console.log(data);
    let sql = `update category set name='${data.name}' where id=${data.id}`
    let [res] = await db.query(sql)
    if (res.affectedRows) {
        return { code: 1, msg: '修改成功', data: '' }
    } else {
        return { code: 0, msg: '修改失败', data: '' }
    }
}
async function mod1(data) {
    console.log(data);
    let sql = `update category set imgurl='${data.imgurl}' where id=${data.id}`
    let [res] = await db.query(sql)
    if (res.affectedRows) {
        return { code: 1, msg: '修改成功', data: '' } 
    } else {
        return { code: 0, msg: '修改失败', data: '' }
    }
}
  

async function del(data) {
    let sql = `SELECT * FROM goods where cid=${data.id}`
    let [res] = await db.query(sql)
   if (res.length==0) {
       let sql1=`DELETE FROM category WHERE id=${data.id}` 
       let [sql2] = await db.query(sql1) 
       if (sql2.affectedRows) {
        return { code: 1, msg: '删除成功', data: '' }
    } else {
        return { code: 0, msg: '删除失败', data: '' }
    }
   }
   return { code: 0, msg: '该分类里存在商品无法删除', data: '' }
  
 
}

module.exports = {add ,list, query,mod,mod1,del,list1}